﻿using System;
using System.Data.OracleClient;
using System.Data;
using System.Collections.Generic;
using System.Reflection;

namespace HiStaff.Dal
{
    public sealed class OracleHelper
    {
        #region Public function
        public static DataSet ExcuteSelectDataSet(string packname, string procname, Object parameter)
        {
            try
            {
                List<USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);

                OracleConnection conn = DBConnection.GetConnection();
                if (conn.State != ConnectionState.Open)
                    return new DataSet();

                OracleCommand command = conn.CreateCommand();

                if (!string.IsNullOrEmpty(packname))
                    command.CommandText = packname + "." + procname;
                else command.CommandText = procname;

                command.CommandType = CommandType.StoredProcedure;

                DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);

                DataSet dsReturn = new DataSet("S");
                OracleDataAdapter adap = new OracleDataAdapter(command);
                adap.Fill(dsReturn);

                List<USER_ARGUMENTS> lstRefCur = lstUserArg.FindAll(obj => obj.DATA_TYPE == DalUtility.ORACLE_CURSOR_TYPE);

                for (int idx = 0; idx < lstRefCur.Count; idx++)
                {
                    dsReturn.Tables[idx].TableName = lstRefCur[idx].ARGUMENT_NAME;
                }

                return dsReturn;
            }
            catch (Exception ex)
            { throw ex; }
        }
        public static DataSet ExcuteSelectDataSet(string packname, string procname)
        {
            return ExcuteSelectDataSet(packname, procname, new Object());
        }
        public static DataSet ExcuteSelectDataSet(string procname, Object parameter)
        {
            return ExcuteSelectDataSet(string.Empty, procname, parameter);
        }
        public static DataSet ExcuteSelectDataSet(string procname)
        {
            return ExcuteSelectDataSet(procname, new Object());
        }
        
        public static DataTable ExcuteSelectDataTable(string packname, string procname, Object parameter)
        {
            try
            {
                DataTable dt = new DataTable();

                List<USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);

                OracleConnection conn = DBConnection.GetConnection();
                OracleCommand command = conn.CreateCommand();

                if (!string.IsNullOrEmpty(packname))
                    command.CommandText = packname + "." + procname;
                else command.CommandText = procname;

                command.CommandType = CommandType.StoredProcedure;

                DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);

                OracleDataAdapter adapter = new OracleDataAdapter(command);

                adapter.Fill(dt);

                USER_ARGUMENTS lstRefCur = lstUserArg.Find(obj => obj.DATA_TYPE == DalUtility.ORACLE_CURSOR_TYPE);
                dt.TableName = lstRefCur.ARGUMENT_NAME;
                return dt;
            }
            catch (Exception ex)
            {
                DBConnection.Close();
                Log.WriteExceptionLog(ex, string.Format("{0}({1}, {2}, {3})", "ExcuteSelectToDataSet", packname, procname, parameter.GetType().Name));
                return new DataTable();
            }
        }
        public static DataTable ExcuteSelectDataTable(string packname, string procname)
        {
            return ExcuteSelectDataTable(packname, procname, new Object());
        }
        public static DataTable ExcuteSelectDataTable(string procname, Object parameter)
        {
            return ExcuteSelectDataTable(string.Empty, procname, parameter);
        }
        public static DataTable ExcuteSelectDataTable(string procname)
        {
            return ExcuteSelectDataTable(procname, new Object());
        }

        public static List<T> ExcuteSelectMultiObject<T>(string packname, string procname, Object parameter) where T : new()
        {
            try
            {
                List<USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);

                OracleConnection conn = DBConnection.GetConnection();
                OracleCommand command = conn.CreateCommand();

                if (!string.IsNullOrEmpty(packname))
                    command.CommandText = packname + "." + procname;
                else command.CommandText = procname;

                command.CommandType = CommandType.StoredProcedure;

                DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);

                return DalUtility.LoadObjectListFromDatabase<T>(command);
            }
            catch (Exception ex)
            {
                DBConnection.Close();
                Log.WriteExceptionLog(ex, string.Format("{0}({1}, {2}, {3})", "ExcuteSelectMultiObject", packname, procname, parameter.GetType().Name));
                return new List<T>();
            }
        }
        public static List<T> ExcuteSelectMultiObject<T>(string packname, string procname) where T : new()
        {
            return ExcuteSelectMultiObject<T>(packname, procname, new Object());
        }
        public static List<T> ExcuteSelectMultiObject<T>(string procname, Object parameter) where T : new()
        {
            return ExcuteSelectMultiObject<T>(string.Empty, procname, parameter);
        }
        public static List<T> ExcuteSelectMultiObject<T>(string procname) where T : new()
        {
            return ExcuteSelectMultiObject<T>(procname, new Object());
        }

        public static T ExcuteSelectObject<T>(string packname, string procname, Object parameter) where T : new()
        {
            try
            {
                List<USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);

                OracleConnection conn = DBConnection.GetConnection();
                OracleCommand command = conn.CreateCommand();

                if (!string.IsNullOrEmpty(packname))
                    command.CommandText = packname + "." + procname;
                else command.CommandText = procname;

                command.CommandType = CommandType.StoredProcedure;

                DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);

                return DalUtility.LoadObjectFromDatabase<T>(command);
            }
            catch (Exception ex)
            {
                DBConnection.Close();
                Log.WriteExceptionLog(ex, string.Format("{0}({1}, {2}, {3})", "ExcuteSelectObject", packname, procname, parameter.GetType().Name));
                return default(T);
            }
        }
        public static T ExcuteSelectObject<T>(string packname, string procname) where T : new()
        {
            return ExcuteSelectObject<T>(packname, procname, new Object());
        }
        public static T ExcuteSelectObject<T>(string procname, Object parameter) where T : new()
        {
            return ExcuteSelectObject<T>(string.Empty, procname, parameter);
        }
        public static T ExcuteSelectObject<T>(string procname) where T : new()
        {
            return ExcuteSelectObject<T>(procname, new Object());
        }

        public static bool ExcuteNonQuery(string packname, string procname, Object parameter)
        {
            try
            {
                int resutl;

                List<USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);

                OracleConnection conn = DBConnection.GetConnection();
                OracleCommand command = conn.CreateCommand();

                if (!string.IsNullOrEmpty(packname))
                    command.CommandText = packname + "." + procname;
                else command.CommandText = procname;

                command.CommandType = CommandType.StoredProcedure;

                DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);

                resutl = command.ExecuteNonQuery();

                DalUtility.SetOutputValueToObject(command, parameter);

                return true;
            }
            catch (Exception ex)
            {
                DBConnection.Close();
                Log.WriteExceptionLog(ex, string.Format("{0}({1}, {2}, {3})", "ExcuteNonQuery", packname, procname, parameter.GetType().Name));
                return false;
            }
        }
        public static bool ExcuteNonQuery(string packname, string procname)
        {
            return ExcuteNonQuery(packname, procname, new Object());
        }
        public static bool ExcuteNonQuery(string procname, Object parameter)
        {
            return ExcuteNonQuery(string.Empty, procname, parameter);
        }
        public static bool ExcuteNonQuery(string procname)
        {
            return ExcuteNonQuery(procname, new Object());
        }
        #endregion

        #region Private function

        #endregion
    }
}